<?php
/** 
*
* @package phpBB3
* 
* @author David Lewis ( http://startrekguide.com ) - Highway of Life
* @author Igor ( http://phpbbmodders.net ) - eviL3
* 
* @version $Id: index.php 68 2007-05-23 16:45:34Z highwayoflife $
* @copyright (c) 2007 eviL3 and Highway of Life
* @license http://opensource.org/licenses/gpl-license.php GNU Public License 
*
*/

/**
* @ignore
*/
define('IN_PHPBB', true);
$phpbb_root_path = '../';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include($phpbb_root_path . 'common.' . $phpEx);
include($phpbb_root_path . 'cvsdb/functions_cvsdb.' . $phpEx);

define('CVSDB_TABLE', $table_prefix . 'cvsdb');
$cvsdb_version = '0.0.2';

// Start session management
$user->session_begin();
$auth->acl($user->data);

$sql = 'SELECT style_id FROM ' . STYLES_TABLE . " WHERE style_name = 'prosilver'";
$db->sql_query($sql, 2678400); // caching it for a LONG time (31 days).. :)
// the question is... is it faster to query than to cache? unsure.
$style = $db->sql_fetchfield('style_id');

$user->setup('cvsdb', $style);

$mode = request_var('mode', '');

// delete, add and edit actions will be determined here.
$action = request_var('action', '');
$submit = isset($_REQUEST['submit']) ? true : false;

// validate the mode
if (!in_array($mode, array('add', 'edit', 'delete')))
{
	$mode = '';
}

// check to see if the cvsdb is installed. If not, go ahead and install it.
if (!isset($config['cvsdb_version']))
{
	install_cvsdb();
}
else if (version_compare($config['cvsdb_version'], $cvsdb_version, '<'))
{
	update_cvsdb();
}

$error = $s_hidden_fields = array();

$template->assign_vars(array(
	'U_CVSDB'		=> append_sid("{$phpbb_root_path}cvsdb/index.$phpEx"),
	'S_FORM_ACTION'	=> append_sid("{$phpbb_root_path}cvsdb/index.$phpEx"),
	'S_AUTH_MANAGE'	=> $auth->acl_get('m_manage_cvsdb'),
	
	'NEW_POST_IMG'	=> $user->img('button_topic_new', 'NEW_CVSDB')
));

// Load the appropriate faq file
switch ($mode)
{
	case 'add':
	case 'edit':
	case 'delete':
		
		if (!$auth->acl_get('m_manage_cvsdb'))
		{
			// this user does not have permission to manage the CVSDB, we will not send them to mode list, we will trigger_error
			trigger_error('NOT_ALLOWED_MODE');
		}
		
		if ($mode == 'edit' || $mode == 'delete')
		{
			$cvsdb_id = request_var('id', 0);
			
			$sql = 'SELECT cvsdb_id
				FROM ' . CVSDB_TABLE . '
				WHERE cvsdb_id = ' . $cvsdb_id;
			$result = $db->sql_query($sql);
			$cvsdb_id = $db->sql_fetchfield('cvsdb_id');
			$db->sql_freeresult($result);
			
			if (!$cvsdb_id)
			{
				trigger_error('NO_CVSDB_ID');
			}
			
			if ($mode == 'delete')
			{
				// mode=delete
				if (isset($_POST['cancel']))
				{
					redirect(append_sid("{$phpbb_root_path}cvsdb/index.$phpEx"));
				}
				
				if (!confirm_box(true))
				{
					confirm_box(false, 'CVSDB_DELETE', build_hidden_fields(array(
						'mode'	=> $mode,
						'id'	=> $cvsdb_id,
					)));
				}
				
				$sql = 'DELETE FROM ' . CVSDB_TABLE . ' WHERE cvsdb_id = ' . $cvsdb_id;
				$db->sql_query($sql);
				
				trigger_error($user->lang['ENTRY_DELETED'] . '<br /><br />' . sprintf($user->lang['RETURN_CVSDB'], '<a href="' . append_sid("{$phpbb_root_path}cvsdb/index.$phpEx") . '">', '</a>'));
			}
		}
		
		if ($submit)
		{
			$text = utf8_normalize_nfc(request_var('cvsdb_sql', '', true));
			$uid = $bitfield = $options = '';
			generate_text_for_storage($text, $uid, $bitfield, $options);
			
			$row = array(
				'cvsdb_id'			=> isset($cvsdb_id) ? $cvsdb_id : 0,
				'cvsdb_sql'			=> $text,
				'cvsdb_cvs_date'	=> request_var('cvsdb_cvs_date', ''),
				//'cvsdb_db_type'		=> request_var('cvsdb_db_type', 0),
			);
			
			if ($mode == 'add')
			{
				$row = array_merge($row, array(
					'cvsdb_poster_id'	=> $user->data['user_id'],
					'cvsdb_time'		=> time(),
				));
			}
			
			if (!cvsdb_validate_date($row['cvsdb_cvs_date']))
			{
				$error[] = $user->lang['CVSDB_ERR_INVALID_DATE'];
			}
			
			if (empty($row['cvsdb_sql']))
			{
				$error[] = $user->lang['NO_SQL_ENTERED'];
			}
			
			if (!sizeof($error))
			{
				$row['cvsdb_cvs_date'] = cvsdb_convert_date($row['cvsdb_cvs_date']);
				
				$sql = ($mode == 'edit') ? 'UPDATE ' . CVSDB_TABLE . ' SET ' . $db->sql_build_array('UPDATE', $row) . ' WHERE cvsdb_id = ' . $cvsdb_id : 'INSERT INTO ' . CVSDB_TABLE . ' ' . $db->sql_build_array('INSERT', $row);
				$db->sql_query($sql);
				
				trigger_error($user->lang['ENTRY_' . (($mode == 'add') ? 'ADDED' : 'UPDATED')] . '<br /><br />' . sprintf($user->lang['RETURN_CVSDB'], '<a href="' . append_sid("{$phpbb_root_path}cvsdb/index.$phpEx") . '">', '</a>'));
			}
		}
		else
		{
			if ($mode == 'edit')
			{
				// mode=edit
				$sql = 'SELECT * FROM ' . CVSDB_TABLE . ' WHERE cvsdb_id = ' . $cvsdb_id;
				$result = $db->sql_query($sql);
				$row = $db->sql_fetchrow($result);
				$db->sql_freeresult($result);
			}
			else
			{
				// mode=add
				$row = array(
					'cvsdb_id'			=> 0,
					'cvsdb_sql'			=> '',
					'cvsdb_cvs_date'	=> time(),
					'cvsdb_db_type'		=> 0,
				);
			}
			
			$row['cvsdb_cvs_date'] = date('d-m-Y', $row['cvsdb_cvs_date']);
		}
		
		$s_hidden_fields['mode'] = $mode;
		if (isset($cvsdb_id))
		{
			$s_hidden_fields['id'] = $cvsdb_id;
		}
		
		$template->assign_vars(array(
			'TITLE'			=> $user->lang[strtoupper($mode) . '_TITLE'],
			'TITLE_EXPLAIN'	=> $user->lang[strtoupper($mode) . '_TITLE_EXPLAIN'],
			
			'CVS_DATE'		=> $row['cvsdb_cvs_date'],
			'SQL'			=> generate_text_for_edit($row['cvsdb_sql'], '', 0),
		));
		
		break;
	
	default:
		
		// dd-mm-yyyy
		$start_date		= request_var('start_date', '');
		$end_date		= request_var('end_date', '');
		$display_plain	= isset($_REQUEST['display_plain']) ? (bool) $_REQUEST['display_plain'] : false;
		
		if (!$start_date && !$end_date)
		{
			$start_day		= request_var('start_day', 0);
			$start_month	= request_var('start_month', 0);
			$start_year		= request_var('start_year', 0);
			$end_day		= request_var('end_day', 0);
			$end_month		= request_var('end_month', 0);
			$end_year		= request_var('end_year', 0);
			
			$start_date 	= "$start_day-$start_month-$start_year";
			$end_date		= "$end_day-$end_month-$end_year";
		}
		
		if ((!$start_date && !$end_date) || ($start_date == '0-0-0' && $end_date == '0-0-0'))
		{
			$sql = 'SELECT c.*, u.user_id, u.username, u.user_colour
				FROM ' . CVSDB_TABLE . ' c
				LEFT JOIN ' . USERS_TABLE . ' u
					ON (u.user_id = c.cvsdb_poster_id)
				ORDER BY c.cvsdb_cvs_date DESC';
			
			unset($start_date, $end_date);
		}
		else
		{
			// Igor, we need some better way to determine if there was no date specified.
			// if there is no date specefied, we need to show the last 20 entries
			if (!cvsdb_validate_date($start_date, $error) || !cvsdb_validate_date($end_date, $error))
			{
				$error[] = $user->lang['CVSDB_ERR_INVALID_DATE'];
			}
			else if (cvsdb_convert_date($start_date) > cvsdb_convert_date($end_date))
			{
				$error[] = $user->lang['CVSDB_ERR_BADSIZE_DATE'];
			}
			
			if (sizeof($error))
			{
				$mode = '';
				break;
			}
			else
			{
				$db_start_date = cvsdb_convert_date($start_date);
				$db_end_date = cvsdb_convert_date($end_date);
			}
			
			$db_start_date = ($db_start_date == $db_end_date) ? $db_start_date - 86400 : $db_start_date;
			
			// Thanks to Highway of Life for the BETWEEN stuff
			$sql = 'SELECT c.*, u.user_id, u.username, u.user_colour
				FROM ' . CVSDB_TABLE . ' c
				LEFT JOIN ' . USERS_TABLE . " u
					ON (c.cvsdb_poster_id = u.user_id)
				WHERE c.cvsdb_cvs_date BETWEEN $db_start_date AND $db_end_date
				ORDER BY c.cvsdb_cvs_date DESC";
		}
		
		$result = $db->sql_query($sql);
		
		$sql_data = '';
		$row_count = 0;
		while ($row = $db->sql_fetchrow($result))
		{
			$row['cvsdb_sql'] = generate_text_for_display($row['cvsdb_sql'], '', '', 0);
			
			if ($display_plain)
			{
				$sql_data .= (!empty($sql_data) ? "\n" : '') . $row['cvsdb_sql'];
			}
			else
			{
				$template->assign_block_vars('row', array(
					'CVS_DATE'		=> $user->format_date($row['cvsdb_cvs_date']),
					'POSTER_FULL'	=> get_username_string('full', $row['user_id'], $row['username'], $row['user_colour']),
					'SQL_DATA'		=> str_replace("\n", "\n<br />\n", $row['cvsdb_sql']),
					
					'U_EDIT'		=> append_sid("{$phpbb_root_path}cvsdb/index.$phpEx", "mode=edit&amp;id={$row['cvsdb_id']}"),
					'U_DELETE'		=> append_sid("{$phpbb_root_path}cvsdb/index.$phpEx", "mode=delete&amp;id={$row['cvsdb_id']}"),
				));
			}
		
			$row_count++;
		}
		
		$db->sql_freeresult($result);
		
		$template->assign_vars(array(
			'SQL_DATA'			=> $sql_data,
			'SQL_STATS'			=> sprintf($user->lang['SQL_STATS'], $row_count),
			
			'U_NEW'				=> append_sid("{$phpbb_root_path}cvsdb/index.$phpEx", 'mode=add'),
			'S_DISPLAY_PLAIN'	=> $display_plain,
			'S_ROW_COUNT'		=> $row_count,
		));
		
		if (!$row_count)
		{
			$error[] = $user->lang['NO_CVS'];
			$mode = '';
			break;
		}
		
		break;
}

if (!isset($start_date))
{
	$start_date = date('d-m-Y', time() - 86400); // - 1 day
}
if (!isset($end_date))
{
	$end_date = date('d-m-Y');
}

cvsdb_date_select($start_date, $start_day_select, $start_month_select, $start_year_select);
cvsdb_date_select($end_date, $end_day_select, $end_month_select, $end_year_select);

// Lets build a page ...
$template->assign_vars(array(
	'S_ERROR'				=> (sizeof($error)) ? implode('<br />', $error) : '',
	'S_HIDDEN_FIELDS'		=> (sizeof($s_hidden_fields)) ? build_hidden_fields($s_hidden_fields) : '',
	'S_MODE'				=> $mode,
	
	'S_START_DAY_SELECT'	=> $start_day_select,
	'S_START_MONTH_SELECT'	=> $start_month_select,
	'S_START_YEAR_SELECT'	=> $start_year_select,
	
	'S_END_DAY_SELECT'		=> $end_day_select,
	'S_END_MONTH_SELECT'	=> $end_month_select,
	'S_END_YEAR_SELECT'		=> $end_year_select,
));

page_header($user->lang['CVSDB']);

$template->set_filenames(array(
	'body' => 'cvsdb_body.html')
);

page_footer();

?>